package com.timesheet.server.core.util;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.timesheet.server.core.mapper.Mapper3;
import com.timesheet.server.core.models.EffortTracker;
import com.timesheet.server.core.models.LeaveRecord;
import com.timesheet.server.core.models.TaskTracker;

public class PMManager {

	public static List getAllEffortsInBetweenDates(Date fromDate, Date toDate) {

		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();

		String sql = "SELECT * FROM EFFORTTRACKER eff where eff.dayDate  ";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		query.setParameter("from", fromDate);
		query.setParameter("to", toDate);
		List results = query.list();
		System.out.println(results);

		return query.list();

	}

	public static boolean persist(Object obj) throws Exception {
		HibernateUtil hibenateUtil = new HibernateUtil();
		boolean isSuccess = false;
		Session session = null ;
		try {
			session = hibenateUtil.getSession();
			Transaction t = session.beginTransaction();
			session.save(obj);
			t.commit();
			session.close();
			isSuccess = true;
			System.out.println("successfully saved");
		} catch (Exception ex) {
			ex.printStackTrace();
			isSuccess = false;
			session.close();
			throw new Exception();
		}

		return isSuccess;

	}

	public static boolean load(EffortTracker effortTracker) {
		System.out.println("ID::::" + effortTracker.getId());
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		EffortTracker effortTrackerCpy = (EffortTracker) session.get(
				EffortTracker.class, effortTracker.getId());
		Mapper3.map(effortTrackerCpy, effortTracker);
		// effortTrackerCpy.setTaskType(effortTracker.getTaskType());
		transaction.commit();
		System.out.println("Successfully updated");

		return false;

	}

	public static boolean delete(EffortTracker effortTracker) throws Exception {
		boolean isSuccess = false;
		try {
			System.out.println("ID::::" + effortTracker.getId());
			HibernateUtil hibenateUtil = new HibernateUtil();
			Session session = hibenateUtil.getSession();
			Transaction transaction = session.beginTransaction();
			session.delete(effortTracker);
			transaction.commit();
			System.out.println("Successfully deleted");
			isSuccess = true;
		} catch (Exception ex) {
			ex.printStackTrace();
			isSuccess = false;
			throw new Exception();
		}
		return isSuccess;

	}

	public static boolean update(EffortTracker effortTracker) {
		System.out.println("ID::::" + effortTracker.getId());
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		EffortTracker effortTrackerCpy = (EffortTracker) session.get(
				EffortTracker.class, effortTracker.getId());
		Mapper3.map(effortTrackerCpy, effortTracker);
		// effortTrackerCpy.setTaskType(effortTracker.getTaskType());
		transaction.commit();
		System.out.println("Successfully updated");

		return false;

	}

	public static boolean update2(EffortTracker effortTracker) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		String query = "from EffortTracker eff where eff.taskNo = :taskNo AND eff.taskType=:taskType AND eff.taskPhase=:taskPhase AND eff.employeeId=:employeeId AND"
				+ " eff.projectId=:projectId AND eff.dayDate=:dayDate";
		Query q = session.createQuery(query);
		q.setParameter("taskNo", effortTracker.getTaskNo());
		q.setParameter("taskType", effortTracker.getTaskType());
		q.setParameter("taskPhase", effortTracker.getTaskPhase());
		q.setParameter("employeeId", effortTracker.getEmployeeId());
		q.setParameter("projectId", effortTracker.getProjectId());
		q.setParameter("dayDate", effortTracker.getDayDate());

		if (q.list() != null && q.list().size() > 0) {

			Object object = q.list().get(0);
			System.out.println(object);
			if (object instanceof EffortTracker) {
				EffortTracker eff = (EffortTracker) object;
				session.update(eff);
				transaction.commit();
				return true;
			}

		} else {
			return false;
		}
		return false;

	}

	public boolean findDuplicate2(EffortTracker effortTracker) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		String query = "from EffortTracker eff where eff.taskNo = :taskNo AND eff.taskType=:taskType AND eff.taskPhase=:taskPhase AND eff.employeeId=:employeeId AND"
				+ " eff.projectId=:projectId AND eff.dayDate=:dayDate";
		Query q = session.createQuery(query);
		q.setParameter("taskNo", effortTracker.getTaskNo());
		q.setParameter("taskType", effortTracker.getTaskType());
		q.setParameter("taskPhase", effortTracker.getTaskPhase());
		q.setParameter("employeeId", effortTracker.getEmployeeId());
		q.setParameter("projectId", effortTracker.getProjectId());
		q.setParameter("dayDate", effortTracker.getDayDate());
		if (q.list() != null && q.list().size() > 0) {
			System.out.println(q.list());
			return true;
		} else {
			return false;
		}

	}

	public static List getDailzEffortsForAWeekend(String empId, String projId,
			Date weekDate) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		String query = "from EffortTracker eff where eff.employeeId=:employeeId and eff.projectId=:projectId and eff.weekDate=:weekDate";
		Query query2 = session.createQuery(query);
		query2.setParameter("projectId", projId);
		query2.setParameter("employeeId", empId);
		query2.setParameter("weekDate", weekDate);
		return query2.list();
	}

	public static List getWSR(String empId, String projId, Date weekDate) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		String query1 = "select eff.taskType,eff.taskNo,sum(eff.hours),eff.taskDescription from EffortTracker eff where eff.employeeId=:employeeId and eff.projectId=:projectId and eff.weekDate=:weekDate GROUP BY eff.taskNo ";
		Query query2 = session.createQuery(query1);

		query2.setParameter("projectId", projId);
		query2.setParameter("employeeId", empId);
		query2.setParameter("weekDate", weekDate);
		return query2.list();
	}

	public static List getUtilisation(String projId) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		String query1 = "select eff.id,eff.projectId,sum(eff.hours) from EffortTracker eff where eff.projectId='ABSLIFE' GROUP BY eff.projectId  ";
		EffortTracker effortTracker = new EffortTracker();
		Query query2 = session.createQuery(query1);
		List<Double> sumList = new ArrayList<Double>();
		double sum = 0;
		return query2.list();

	}

	public static List getWSR(String empId, String projId, String weekDate) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();

		String sql = "SELECT eff.taskType,eff.taskNo,SUM(hours),eff.taskDescription from EffortTracker eff where eff.employeeId='"
				+ empId
				+ "' and eff.projectId='"
				+ projId
				+ "' and eff.WEEK_DATE='" + weekDate + "'  ";

		System.out.println(sql);

		Query query2 = session.createSQLQuery(sql);
		/*
		 * query2.setParameter("projectId", projId);
		 * query2.setParameter("employeeId", empId);
		 * query2.setParameter("weekDate", weekDate);
		 */
		List<?> list = query2.list();
		// System.out.println(list.get(0));
		return list;
	}

	public static List getAll(Class className) {
		HibernateUtil hibenateUtil = new HibernateUtil();
		Session session = hibenateUtil.getSession();
		return session.createQuery("from " + className.getName()).list();
	}

	public static void main(String[] args) throws Exception {
/*
		EffortTracker effortTracker = new EffortTracker();
		//effortTracker.setId(new Long(1));
		effortTracker.setTaskType("Technical tgasks");
		PMManager.persist(effortTracker);
*/
		
		
		TaskTracker t = new TaskTracker();
		t.setTaskNo("fff");
		LeaveRecord leaveDTO = new LeaveRecord();
		
		
		leaveDTO.setEmployeeId("bin");
		leaveDTO.setDayDate(Calendar.getInstance().getTime());
		leaveDTO.setLeaveType("d");
		PMManager.persist( leaveDTO);
		

	}
}
